package com.bright.ghj.overt.util;

import com.bright.ghj.common.util.DateUtil;
import com.bright.ghj.common.util.StringUtil;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author hxj
 * @Date 2024/2/2 17:59
 * @Description Access数据库工具类
 */
@Slf4j
public class AccessDBUtil {

    // 根据access文件建立数据库连接
    public static Connection initConnection(File file, String userName, String password) {
//        this.file = file;
//        this.accessUserName = userName;
//        this.accessPassword = password;

        Connection con = null;

//        try {
//            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
//        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException e) {
//            log.error("加载jdbc access类失败:" + e.getMessage());
////        } catch (Exception ex) {
////            log.error("连接第三方数据库失败！", ex);
//        }
        try {
//            con = DriverManager.getConnection(driverName + ";DBQ=" + StringUtil.replace(file.getPath(), "\\", "/"), userName, password);
            con = DriverManager.getConnection("jdbc:ucanaccess://" + StringUtil.replace(file.getPath(), "\\", "/"), userName, password);
            DatabaseMetaData dma = con.getMetaData();
            log.debug("准备加载access数据库:" + dma.getURL());
        } catch (SQLException e) {
            log.error("连接access数据库失败" + file.getPath() + " " + e.getMessage());
            System.out.println("连接access数据库失败" + file.getPath() + " " + e.getMessage());
        }
        if (con != null) {
            log.debug("连接'" + file.getName() + "'数据库成功");
        }

        return con;

    }

    public static boolean execSql(Connection con, String sqlstr)
            throws Exception {
        if (con == null) {
            log.error("执行语句工具类: 连接不存在,操作取消!");
            throw new Exception("数据未连接,操作取消");
        }

        Statement sm = null;
        try {//TYPE_SCROLL_INSENSITIVE   sql2008不支持
            sm = con.createStatement(
                    ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);
            int rows = sm.executeUpdate(sqlstr);
            log.debug("执行语句:" + sqlstr + ", 共" + rows + "行");
        } catch (SQLException e) {
            log.error("执行语句发生错误:" + sqlstr + ",操作取消;", e);
        } finally {
            try {
                sm.close();
            } catch (SQLException e) {
                log.error("关闭执行语句发生异常:" + sqlstr + ";" + e.getMessage());
            }

        }

        return true;
    }

    public static boolean execSql(Statement sm, String sqlstr)
            throws Exception {
        if (sm == null) {
            log.error("执行语句工具类: 连接不存在,操作取消!");
            throw new Exception("数据未连接,操作取消");
        }

        try {
            int rows = sm.executeUpdate(sqlstr);
            log.debug("执行语句:" + sqlstr + ", 共" + rows + "行");
        } catch (SQLException e) {
            log.error("执行语句发生错误:" + sqlstr + ",操作取消;" + e.getMessage());
        }
        return true;
    }
	
	/*public static ResultSet openSql(Connection con, String sqlstr) throws Exception {
		if (con == null) {
			log.error("执行语句工具类: 连接不存在,操作取消!");
			throw new Exception("数据未连接,操作取消");
		}
		 
		
		return openSql(getStatement(con), sqlstr);
	}*/

    public static ResultSet openSql(Statement sm, String sqlstr) throws Exception {
        //log.debug("openSql(Statement sm, String sqlstr)  -- start");
        if (sm == null) {
            log.error("数据集为空, 跳过关闭数据集");
            throw new Exception("数据未连接,操作取消");
        }

        ResultSet rs = null;
//        try {
            rs = sm.executeQuery(sqlstr);
//        } catch (SQLException e) {
//            log.debug("执行查询语句失败:"+ sqlstr + ";", e);
//            e.printStackTrace();
//        }


        return rs;
    }

    public static PreparedStatement getPreparedStatement(Connection con, String sql) throws Exception {
        if (con == null) {
            log.error("执行语句工具类: 连接不存在,操作取消!");
            throw new Exception("数据未连接,操作取消");
        }

        PreparedStatement psm = null;
        try {
            psm = con.prepareStatement(sql);
        } catch (SQLException e) {
            log.error("创建数据集失败:", e);
        }

        return psm;
    }


    public static Statement getStatement(Connection con) throws Exception {
        if (con == null) {
            log.error("执行语句工具类: 连接不存在,操作取消!");
            throw new Exception("数据未连接,操作取消");
        }

        Statement sm = null;
        try {
            sm = con.createStatement();
        } catch (SQLException e) {
            log.error("创建数据集失败:", e);
        }

        return sm;
    }

    public static boolean closePreparedStatement(PreparedStatement psm) {
        if (psm == null) {
            //log.debug("数据集为空, 跳过关闭数据集");
            return true;
        }
        try {
            psm.close();
        } catch (SQLException e) {
            log.error("关闭数据集出错:", e);
        }
        return true;
    }

    public static boolean closeStatement(Statement sm) {
        if (sm == null) {
            //log.debug("数据集为空, 跳过关闭数据集");
            return true;
        }
        try {
            sm.close();
            sm = null;
        } catch (SQLException e) {
            log.error("关闭数据集出错:", e);
        }
        return true;
    }

    public static boolean closeResultSet(ResultSet rs) {
        if (rs == null) {
            //log.debug("数据集为空, 跳过关闭数据集");
            return true;
        }
        try {
            rs.close();
        } catch (SQLException e) {
            log.error("关闭数据集出错:", e);
        }
        return true;
    }

    public static List<List<Object>> getList(Statement sm, String sql) throws Exception {
        List<List<Object>> rvalue = null;
        ResultSet rs = null;

        try {
            rs = openSql(sm, sql);
            rvalue = getList(rs);
//        } catch (Exception e) {
//            log.error("sql得到List出错:" + sql, e);
        } finally {
            closeResultSet(rs);
        }

        return rvalue;
    }


    private static List<List<Object>> getList(ResultSet rs) {
        ResultSetMetaData rsmd = null;
        List list = new ArrayList(), listSub;
        int numberOfColumns, i = 0;
        String date = null;
        BigDecimal decimal = new BigDecimal("0.00");

        try {
            rsmd = rs.getMetaData();
            numberOfColumns = rsmd.getColumnCount();

            while (rs.next()) {
                listSub = new ArrayList();
                for (i = 1; i <= numberOfColumns; i++) {
                    switch (rsmd.getColumnType(i)) {
                        case Types.ARRAY:
                            listSub.add(rs.getArray(i));
                            break;
                        case Types.BIGINT:
                            listSub.add(rs.getLong(i));
                            break;
                        case Types.BINARY:
                            listSub.add(rs.getArray(i));
                            break;
                        case Types.BIT:
                        case Types.BOOLEAN:
                            listSub.add(rs.getBoolean(i));
                            break;
                        case Types.BLOB:
                            listSub.add(rs.getBlob(i));
                            break;
                        case Types.CHAR:
                        case Types.LONGVARCHAR:
                        case -16:
                        case -15:
                        case -9:
                            listSub.add(rs.getString(i));
                            break;
                        case Types.CLOB:
                            listSub.add(rs.getClob(i));
                            break;
                        case Types.DATALINK:
                            listSub.add(rs.getString(i));
                            break;
                        case Types.DATE:
                            date = DateUtil.getDateString(rs.getDate(i));
                            listSub.add(date);
                            break;
                        case Types.NUMERIC:
                        case Types.DOUBLE:
                        case Types.FLOAT:
                        case Types.DECIMAL:
                            decimal = rs.getBigDecimal(i);
                            // if(decimal==null) {
                            // decimal = new BigDecimal("0.00");
                            // }
                            if (decimal != null) {
                                decimal = decimal.setScale(2,
                                        BigDecimal.ROUND_HALF_UP);
                            }
                            listSub.add(decimal);
                            break;
                        case Types.DISTINCT:
                            listSub.add(rs.getLong(i));
                            break;
                        case Types.INTEGER:
                        case Types.SMALLINT:
                            listSub.add(rs.getInt(i));
                            break;
                        case Types.NULL:
                            listSub.add(null);
                            break;
                        case Types.REAL:
                            listSub.add(rs.getDouble(i));
                            break;
                        case Types.TIME:
                            date = DateUtil.getDateString(rs.getDate(i));
                            listSub.add(date);
                            break;
                        case Types.TIMESTAMP:
                            date = DateUtil.getDateString(rs.getDate(i));
                            listSub.add(date);
                            break;
                        case Types.TINYINT:
                            listSub.add(rs.getInt(i));
                            break;
                        case Types.VARBINARY:
                            listSub.add(rs.getInt(i));
                            break;
                        case Types.VARCHAR:
                            listSub.add(rs.getString(i));
                            break;
                    }
                }
                list.add(listSub);
            }
        } catch (Exception e) {
            log.error("生成list发生异常 错误发生在第" + i + "字段", e);
            e.printStackTrace();
            try {
                log.error("字段类型：" + rsmd.getColumnType(i));
            } catch (SQLException e1) {
                log.error("获取columnType异常： " + e1.getMessage());
                e1.printStackTrace();
            }
        }

        return list;
    }

    public static List<String> getTableColumns(Statement sm, String tableName) throws Exception {
        List<String> columns = new ArrayList<>();
        ResultSet rs = openSql(sm, "select * from [" + tableName + "]");
        ResultSetMetaData metaData = rs.getMetaData();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            columns.add(metaData.getColumnName(i));
        }

        return columns;
    }

}
